package arquitetura.model;

import java.sql.*;
import java.util.ArrayList;
import java.util.Collection;

import to.Cliente;
import to.Reserva;
import conexao.Conexao;
import conexao.ConexaoImpl;
import exceptions.ConexaoException;
import exceptions.ModelException;

public class ModelReservaImpl implements ModelReserva{
	private Conexao conexao;

	public ModelReservaImpl(){
		conexao = ConexaoImpl.getInstancia();
    }

	public void incluir(Reserva reserva) throws ConexaoException, ModelException {
        Connection conex = conexao.conectar();
        int ultimoCodigo = conexao.ultimoCodigo("reserva");
        
        String sql = "INSERT INTO Reserva (codigo, cliente, data) VALUES(?,?,?)";
        //converte o formatdo Date do java para o formato Date do sql
        java.sql.Date sqlDataReserva = new java.sql.Date(reserva.getData().getTime());
        
        try{
            PreparedStatement pstm = conex.prepareStatement(sql);
            pstm.setInt(1, ultimoCodigo + 1);
            pstm.setInt(2, reserva.getCliente().getCodigo());
            pstm.setDate(2, sqlDataReserva);
            pstm.executeUpdate();
            pstm.close();            
        }catch(SQLException e){
            throw new ModelException(e.getMessage());
        }finally{
            conexao.desconectar(conex);
        }
    }

	public void alterar(Reserva reserva) throws ConexaoException, ModelException {
		Connection conex = conexao.conectar();	        
		java.sql.Date sqlDataReserva = new java.sql.Date(reserva.getData().getTime());

	    String sql = "UPDATE Reserva SET data=?" +
	    			 "WHERE codigo=?";
	    try {
	        PreparedStatement pstm = conex.prepareStatement(sql);
	        pstm.setDate(1, sqlDataReserva);        
	        pstm.setInt(2, reserva.getCodigo());
	        pstm.executeUpdate();
	        pstm.close();
	    } catch (SQLException e) {
	        throw new ModelException(e.getMessage());
	    }finally{
	    	conexao.desconectar(conex);
	    }
	}
	
	public void excluir(int codigo) throws ConexaoException, ModelException {
		Connection conex = conexao.conectar();		
		String sql = "DELETE FROM Reserva WHERE codigo=?";

		try{
			PreparedStatement pstm = conex.prepareStatement(sql);
            pstm.setInt(1, codigo);
            pstm.executeUpdate();
            pstm.close();
        } catch (SQLException e) {
            throw new ModelException(e.getMessage());
        }finally{
        	conexao.desconectar(conex);
        }
	}
	
	public Reserva consultar(int codigo) throws ConexaoException, ModelException {
		Reserva reserva = null;
        Connection conex = conexao.conectar();
        
        String sql = "SELECT codigo, cliente, data FROM reserva WHERE codigo=?";

        try{
            PreparedStatement pstm = conex.prepareStatement(sql);
            pstm.setInt(1, codigo);
            ResultSet rs = pstm.executeQuery();

            if(rs.next()){
            	Cliente cliente = new Cliente();
            	cliente.setCodigo(rs.getInt("cliente"));
            	
            	reserva = new Reserva();
            	reserva.setCodigo(rs.getInt("codigo"));
            	reserva.setCliente(cliente);
            	reserva.setData(rs.getDate("data"));
            }
            
        }catch(SQLException e){
            throw new ModelException(e.getMessage());
            
        }finally{
            conexao.desconectar(conex);
        }
        return reserva;
    }
	
	public Collection<String[]> listarParaGrid(String parteDoNome) throws ConexaoException, ModelException {
    	Collection<String[]> lista = new ArrayList<String[]>();
    	
        Connection conex = conexao.conectar();
        
        String sql = "SELECT codigo, cliente, data FROM reserva";
        
        if (! parteDoNome.trim().equals(""))
			sql = sql + "WHERE data like \"%" + parteDoNome + "%\" ";
		
		sql = sql + "ORDER BY data";
        try{
            Statement stm = conex.createStatement();
            ResultSet rs = stm.executeQuery(sql);
            
            while( rs.next() ){              
                lista.add(new String[] {rs.getString("codigo"), rs.getString("cliente"), rs.getString("data")});
            }
            return lista;
        }catch(SQLException e){
            throw new ModelException(e);
        }finally{
           conexao.desconectar(conex);
        }
    }
}








